merge 可以做到
如果key相同,就update/DELETE
如果key相同 + 條件( and field<>field),就update/DELETE
如果key不存在,就insert
MERGE INTO #tmp1 --要被insert/update/delete的表
USING #tmp2 --被參考的表
ON #tmp1.key1 = #tmp2.key1
--(如果有多key)
ON #tmp1.key1 = #tmp2.key1 and #tmp1.key2 = #tmp2.key2
WHEN MATCHED THEN
UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone
WHEN MATCHED and #tmp1.Name<>#tmp2.Name THEN --除了match可以再加條件
UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone
WHEN MATCHED and #tmp1.Name='' AND #tmp2.Name='' THEN --除了match可以再加條件
DELETE --DELETE只要這樣下就可以了
WHEN NOT MATCHED THEN
INSERT VALUES(#tmp2.co1, #tmp2.col2,#tmp2.col3);
select * into #tmp1 from
(
select co1=1,col2=2,col3=3
union
select co1=1,col2=2,col3=4
union
select co1=1,col2=2,col3=5
)t0
select * into #tmp2 from
(
select co1=1,col2=2,col3=3
union
select co1=1,col2=2,col3=4
union
select co1=1,col2=2,col3=6
)t0
--1.找出tmp1沒有
SELECT * from #tmp2
EXCEPT --在ORACLE的話,用MINUS
SELECT * FROM #tmp1
--2.tmp1沒有,就把它insert到tmp1去
MERGE INTO #tmp1
USING #tmp2
ON #tmp1.co1 = #tmp2.co1 and #tmp1.col2 = #tmp2.col2 and #tmp1.col3 = #tmp2.col3
WHEN NOT MATCHED THEN
INSERT VALUES(#tmp2.co1, #tmp2.col2,#tmp2.col3);
--chk #tmp1是否已新增,1,2,6
select * from #tmp1
select * from #tmp2
select * into #tmp1 from
(
select ID=1,Name=cast( '' as varchar(30)),Phone=3
union
select ID=2,Name='',Phone=4
union
select ID=3,Name='',Phone=5
)t0
select * into #tmp2 from
(
select ID=1,Name='Wiz Khalifa',Phone=30
union
select ID=2,Name='Charlie Puth',Phone=40
union
select ID=3,Name='Furious',Phone=50
union
select ID=4,Name='Soundtrack',Phone=60
)t0
--1.找出tmp1沒有
SELECT * from #tmp2
EXCEPT --在ORACLE的話,用MINUS
SELECT * FROM #tmp1
--2.tmp1沒有,就把它insert到tmp1去
MERGE INTO #tmp1
USING #tmp2
ON #tmp1.ID = #tmp2.ID
WHEN MATCHED and (#tmp1.Name !=#tmp2.Name or #tmp1.Phone!=#tmp2.Phone) THEN
UPDATE SET #tmp1.Name = #tmp2.Name ,#tmp1.Phone = #tmp2.Phone
WHEN NOT MATCHED THEN
INSERT VALUES(#tmp2.ID, #tmp2.Name,#tmp2.Phone);
--chk #tmp1.Name & #tmp1.Phone是否已更新 缺的資料ID=4是否已進入
select * from #tmp1
select * from #tmp2
注意:如果是用update or delete不能多行.mean 要有唯一的key.
sql吐出的錯誤訊息為:MERGE 陳述式嘗試多次 UPDATE 或 DELETE 同一個資料列。這在目標資料列符合多個來源資料列時便會發生。MERGE 陳述式不能多次 UPDATE/DELETE 目標資料表的同一個資料列。請重新定義 ON 子句,以確定目標資料列最多只符合一個來源資料列,或使用 GROUP BY 子句將來源資料列編成群組。
ref
like 對2個庫存資料的解決方法.
https://dotblogs.com.tw/dc690216/2010/01/25/13313